package com.lovo.roomProject.system.dao.impl;

import com.lovo.roomProject.dao.BaseDAO;
import com.lovo.roomProject.pojo.*;
import com.lovo.roomProject.system.dao.SystemDAO;

import java.sql.SQLException;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class SystemDAOImpl extends BaseDAO implements SystemDAO {
    @Override
    public List listUser() {
        this.getConnection();
        String sql="select * from t_user";
        List list =new ArrayList();
        try {
            statement=con.prepareStatement(sql);
            rs=statement.executeQuery();
            while (rs.next()){
                SystemDO systemDO=fillUser();
                list.add(systemDO);
            }
        }catch (SQLException e){
            e.printStackTrace();
        } finally {
            close(rs,statement,con);
        }
        return list;
    }

    @Override
    public SystemDO fillUser() throws SQLException{
        SystemDO systemDO=new SystemDO();
        systemDO.setId(rs.getInt("u_id"));
        systemDO.setUserName(rs.getString("u_user_name"));
        systemDO.setUserPassWord(rs.getString("u_user_password"));
        systemDO.setRealName(rs.getString("u_real_name"));
        systemDO.setGrade(rs.getString("u_grade"));
        return systemDO;
    }

    @Override
    public void insert(SystemDO systemDO) {
        this.getConnection();
        String sql="INSERT INTO t_user (u_user_name,u_real_name,u_grade) VALUES (?,?,?)";
        try {
            statement=con.prepareStatement(sql);
            statement.setObject(1,systemDO.getUserName());
            statement.setObject(2,systemDO.getRealName());
            statement.setObject(3,systemDO.getGrade());
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
    }

    @Override
    public void delete(int id) {
        this.getConnection();
        String sql="delete from t_user where u_id=?";
        try {
            statement=con.prepareStatement(sql);
            statement.setObject(1,id);
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public SystemDO listUser(String userName) {
        this.getConnection();
        String sql="select * from t_user where u_user_name=?";
        SystemDO systemDO=new SystemDO();
        try {
            statement=con.prepareStatement(sql);
            statement.setObject(1,userName);
            rs=statement.executeQuery();
            if (rs.next()){
                systemDO.setUserName(rs.getString("u_user_name"));
                systemDO.setUserPassWord(rs.getString("u_user_password"));
                systemDO.setRealName(rs.getString("u_real_name"));
                systemDO.setGrade(rs.getString("u_grade"));
                systemDO.setId(rs.getInt("u_id"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return systemDO;
    }

    @Override
    public SystemDO listUser(int id) {
        this.getConnection();
        String sql="select * from t_user where u_id=?";
        SystemDO systemDO=new SystemDO();
        try {
            statement=con.prepareStatement(sql);
            statement.setObject(1,id);
            rs=statement.executeQuery();
            if (rs.next()){
                systemDO.setUserName(rs.getString("u_user_name"));
                systemDO.setUserPassWord(rs.getString("u_user_password"));
                systemDO.setRealName(rs.getString("u_real_name"));
                systemDO.setGrade(rs.getString("u_grade"));
                systemDO.setId(rs.getInt("u_id"));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return systemDO;
    }

    @Override
    public CutPageBean listSystemPage(int current) {
        this.getConnection();
        String selectSql=" select *";
        String countSql=" select count(*) cnt ";
        String fromSql=" from t_user";
        String limitSql=" limit ?,?";
        CutPageBean cutPageBean=new CutPageBean<>();
        List list=new ArrayList();
        try {
            statement=con.prepareStatement(selectSql+fromSql+limitSql);
            statement.setObject(1,(current-1)*CutPageBean.PAGESIZE);
            statement.setObject(2,CutPageBean.PAGESIZE);
            rs=statement.executeQuery();
            while (rs.next()){
                SystemDO systemDO=fillUser();
                list.add(systemDO);
            }
            cutPageBean.setList(list);
            //计算总数的SQL
            statement=con.prepareStatement(countSql+fromSql);
            rs=statement.executeQuery();
            rs.next();
            cutPageBean.setCount(rs.getInt("cnt"));
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close(rs,statement,con);
        }
        return cutPageBean;
    }

    @Override
    public void update(SystemDO systemDO) {
        this.getConnection();
        String sql="update t_user set u_grade=? where u_id=?";
        try {
            statement=con.prepareStatement(sql);
            statement.setObject(1,systemDO.getGrade());
            statement.setObject(2,systemDO.getId());
            statement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            close();
        }
    }
}
